package Dao;

import entity.*;
import util.JDBCUtil;
import util.JdbcUtil2;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Roles_PerDao {
    private static Roles_PerDao rolesDao;

    //根据id获取权限
    public List<RolesBean> one(String rname) {
        List<RolesBean> list = null;
        String sql = "SELECT Pname,Plevel FROM roles,roles_per,permission WHERE Rname = ? AND roles.Rid = roles_per.roles_per_RId AND roles_per.roles_per_PId = permission.Pid";
        list = JDBCUtil.list(sql, RolesBean.class, rname);
        return list;
    }

    public List getperFirstChild(int firstParentId) {
        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        List list = new ArrayList();
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //获取执行语句
            String sqlfistChild = "select firstChildID_Parent,Plevel,firstChildPname,secondChildID from firstChild where firstChildID_Parent=" + firstParentId;
            //执行查询操作
            rs = stm.executeQuery(sqlfistChild);
            //遍历结果集
            while (rs.next()) {
                PerFirstChild perFirstChildbean = new PerFirstChild();
                perFirstChildbean.setFirstChildID_Parent(rs.getInt("firstChildID_Parent"));
                perFirstChildbean.setFirstChildPname(rs.getString("firstChildPname"));
                perFirstChildbean.setPlevel(rs.getInt("Plevel"));
                perFirstChildbean.setSecondChildID(rs.getInt("secondChildID"));
                perFirstChildbean.setSecondChild(getperSecondChild(perFirstChildbean.getSecondChildID()));
                list.add(perFirstChildbean);
            }
        } catch (SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    public List getperSecondChild(int secondParentId) {

        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        List list = new ArrayList();
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //获取执行语句
            String sqlSecondChild = "select Plevel,firstChildID_Parent,secondChildPname from secondChild where firstChildID_Parent=" + secondParentId;
            //执行查询操作
            rs = stm.executeQuery(sqlSecondChild);
            //遍历结果集
            while (rs.next()) {
                PerSecondChild perSecondChild = new PerSecondChild();
                perSecondChild.setPlevel(rs.getInt("Plevel"));
                perSecondChild.setSecondChildPname(rs.getString("secondChildPname"));
                perSecondChild.setFirstChildID_Parent(rs.getInt("firstChildID_Parent"));
                list.add(perSecondChild);
            }
        } catch (
                SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    //查询所有角色的全部权限
    public List<Roles_PerBean> all() {
        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        List<Roles_PerBean> list = new ArrayList();
        String sql = "SELECT Rid,Rname,Rstate,Rdescribe FROM roles";
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //执行查询操作
            rs = stm.executeQuery(sql);
            //遍历结果集
            while (rs.next()) {
                Roles_PerBean rolesPerBean = new Roles_PerBean();
                rolesPerBean.setRid(rs.getInt("Rid"));
                rolesPerBean.setRname(rs.getString("Rname"));
                rolesPerBean.setRstate(rs.getString("Rstate"));
                rolesPerBean.setRdescribe(rs.getString("Rdescribe"));
                rolesPerBean.setPermissionBeans(getPmsByRolesId(rolesPerBean.getRid()));
                list.add(rolesPerBean);
            }
        } catch (SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    //查询所有角色的全部权限
    public List<Roles_PerBean> searchByName(String searchName) {
        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        List<Roles_PerBean> list = new ArrayList();
        String sql = "SELECT Rid,Rname,Rstate,Rdescribe FROM roles WHERE Rname like '%"+searchName+"%'";
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //执行查询操作
            rs = stm.executeQuery(sql);
            //遍历结果集
            while (rs.next()) {
                Roles_PerBean rolesPerBean = new Roles_PerBean();
                rolesPerBean.setRid(rs.getInt("Rid"));
                rolesPerBean.setRname(rs.getString("Rname"));
                rolesPerBean.setRstate(rs.getString("Rstate"));
                rolesPerBean.setRdescribe(rs.getString("Rdescribe"));
                rolesPerBean.setPermissionBeans(getPmsByRolesId(rolesPerBean.getRid()));
                list.add(rolesPerBean);
            }
        } catch (SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    public List<PermissionBean> getPmsByRolesId(int rid){
        List<PermissionBean> list = new ArrayList();
        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        //获取sql语句
        String sql = "SELECT Pid,Pname,Ppath,Plevel,firstChildId FROM roles_per,roles,permission where roles_per_Rid = "+rid+" AND Rid= "+rid+" AND roles_per_Pid = Pid";
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //执行查询操作
            rs = stm.executeQuery(sql);
            //遍历结果集
            while (rs.next()) {
                PermissionBean permissionBean = new PermissionBean();
                permissionBean.setPid(rs.getInt("Pid"));
                permissionBean.setPname(rs.getString("Pname"));
                permissionBean.setPpath(rs.getString("Ppath"));
                permissionBean.setPlevel(rs.getInt("Plevel"));
                permissionBean.setFirstChildId(rs.getInt("firstChildId"));
                permissionBean.setFirstChild(getperFirstChild(permissionBean.getFirstChildId()));
                list.add(permissionBean);
            }
        } catch (SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    public int addRoles_Per(int roles_per_RId,int roles_per_PId){
        int row = 0;
        String sql = "insert into roles_per (roles_per_RId,roles_per_PId) values ("+roles_per_RId+","+roles_per_PId+")";
        row = JDBCUtil.update(sql);
        return row;
    }

    public int del_roles_per(int roles_per_RId){
        int row = 0;
        String sql = "DELETE FROM roles_per WHERE roles_per_RId = "+roles_per_RId+"";
        row = JDBCUtil.update(sql);
        return row;
    }

    public static void main(String[] args) {
        List<Roles_PerBean> all = new Roles_PerDao().all();
        for (Roles_PerBean roles_perBean :all) {
            System.out.println(roles_perBean);
        }

    }
}
